import json
import time
import math
import random
import statistics
from apis import api_routes
from flask import abort, request, jsonify, g
from decimal import Decimal, ROUND_DOWN
from security.decoration import certification
from models.logs.logs import Logs
from public.local import date_type
from datetime import datetime, timedelta
from sqlalchemy import and_
from typing import Union
from typing import List
from sqlalchemy import text


@api_routes.route("/barcode", methods=["GET"])
@certification(auth=True, role=None)
def func_barcode_get_api():
    """
    查询产品信息，参数（sequence、task_id、（start、finish）可以自由组合）完成API开发
    查询出来数据类型是个列表， 可以调用 (/api/details GET) 接口查看详细信息
    http://127.0.0.1:8080/api/barcode
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    "start"        string          开始时间, 格式 %Y-%m-%d
    "finish"       string          结束时间  格式 %Y-%m-%d
    "index"         int      ****  第几页，默认为1
    "size"          int      ****  每页数据多少, 默认每页10条数据
    "sequence"     string          产品SN, 不支持模糊查询
    "task_id"       int            工单名称ID，没有此参数则是全部查询   /api/query/task/kind GET
    }
    :return:
        {'status': 200, 'data': resp}
    ---
    tags:
      - CPK 计算
    """
    size: Union[int, None] = request.args.get("size", default=10, type=int)
    index: Union[int, None] = request.args.get("index", default=1, type=int)
    sequence: Union[str, None] = request.args.get("sequence", default=None, type=str)
    start: Union[str, None] = request.args.get("start", default=None, type=str)
    finish: Union[str, None] = request.args.get("finish", default=None, type=str)
    task_id: Union[int, None] = request.args.get("task_id", default=0, type=int)

    date_parm: bool = False
    if start is not None and len(start) != 0 and finish is not None and len(finish) != 0:
        arg1 = datetime.strptime(finish, "%Y-%m-%d") + timedelta(days=1) - timedelta(seconds=1)
        arg0 = datetime.strptime(start, "%Y-%m-%d")
        start: str = arg0.strftime("%Y-%m-%d %H:%M:%S")
        finish: str = arg1.strftime("%Y-%m-%d %H:%M:%S")
        date_parm = True

    if sequence is not None and len(sequence) == 0:
        sequence = None

    sequence_parm: str = "" if sequence is None else "AND courses.sequence = :arg0"
    context_parm: str = "" if date_parm is False else "AND courses.start >= :arg1 AND courses.finish <= :arg2"
    task_parm: str = "" if task_id == 0 else "AND courses.tasks_id = :arg5"

    sql_count: Union[str, None] = """
        SELECT 
            COUNT(courses.id) 
        FROM courses WHERE courses.mark > 0 {} {} {} 
    """
    sql_context: Union[str, None] = """
        SELECT 
            tasks.orders, tasks.name, aircraft.aircraft, courses.sequence,
            COALESCE(TO_CHAR(courses.start,'yyyy-MM-dd HH24:MI:SS'), NULL) as start,
            COALESCE(TO_CHAR(courses.finish,'yyyy-MM-dd HH24:MI:SS'), NULL) as finish,
            courses.mark, courses.repair, courses.message
        FROM tasks INNER JOIN courses On tasks.id = courses.tasks_id
        INNER JOIN aircraft ON courses.aircraft_id = aircraft.id WHERE courses.mark > 0 {} {} {} 
        ORDER BY courses.id LIMIT :arg3 OFFSET :arg4
    """
    sql_count = sql_count.format(sequence_parm, context_parm, task_parm)
    sql_context = sql_context.format(sequence_parm, context_parm, task_parm)

    parm: dict = {
        "arg0": sequence,
        "arg1": start,
        "arg2": finish,
        "arg3": size,
        "arg4": (index - 1) * size,
        "arg5": task_id
    }
    count, = g.session.execute(text(sql_count), parm).fetchone()
    date: list = g.session.execute(text(sql_context), parm).fetchall()
    rsp, page = [], int(count / size) if count / size == int(count / size) else int(count / size) + 1

    marks: dict = {0: "未加工或者未完成", 1: "合格", 2: "不合格", 3: "报废产品"}
    for orders, name, aircraft, sequences, start, finish, mark, repair, message in date:
        rsp.append({
            "orders": orders,
            "name": name,
            "aircraft": aircraft,
            "sequences": sequences,
            "start": start,
            "finish": finish,
            "mark": marks.get(mark),
            "repair": "是" if repair is not None else "否",
            "message": message
        })
    doc: dict = {
        "orders": "工单名称",
        "name": "任务名称",
        "aircraft": "机种名称",
        "sequences": "产品SN",
        "start": "开始加工时间",
        "finish": "结束加工时间",
        "mark": "产品状态",
        "repair": "是否返修机种",
        "message": "产品记录",
    }
    return jsonify({'status': 200, 'data': rsp, "index": index, "size": size, "total": count, "page": page, "doc": doc})
